﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MixScript.Appl.Config;
using MixScript.Appl.Session;

namespace MixScript.DBService
{
    public class cDBService
    {
        #region Property
        private cAppDal dbserver;
        private string _tablename;
        private string _msg;
        private string sSQL;
        private bool _noerror = true;

        public bool NoError
        {
            get { return _noerror; }
            set { _noerror = value; }
        }

        private string _dbname;

        public string DbName
        {
            get { return dbserver.cnfg.Dbname; }
            // set { _dbname = value; }
        }

        private string _serverprovider;
        public string ServerProvider
        {
            get { return dbserver.cnfg.Provider; }

        }

        protected cAppDal Dbserver
        {
            get { return dbserver; }
            set { dbserver = value; }
        }
        public string Msg
        {
            get { return _msg; }
            set { _msg = value; }
        }
        public string Tablename
        {
            get { return _tablename; }
            set { _tablename = value; }
        } 
        #endregion
      

        public cDBService(string sProvider)
        {
            if (sProvider.ToLower() == "sqlserver")
            {
                Dbserver = new cSSDAL();
            }
            else if (sProvider.ToLower() == "mysql")
            {
             //   dbserver = new cMYDAL();
            }
            else
            {
                throw new Exception("Invalid Provider");
            }

        }

        public cDBService()
        {

            cBaseConfig oConfig = new cBaseConfig();
            
            
            if (oConfig.Provider.ToString().ToLower() == "sqlserver")
            {
                Dbserver = new cSSDAL();
            }
            else
            {
              //  dbserver = new cMYDAL();
            }

        }


        #region Logging

        //public void onError(Exception ex, object ob)
        //{

        //    errorLog(userID, ob.GetType().ToString(), "", "", ex);
        //    throw ex;
        //}
        //public void onError(Exception ex, object ob, bool bproperty)
        //{
        //    if (bproperty)
        //    {
        //       // errorLog(LoginName, ob.GetType().ToString(), Sesmenu, Sesrrx, ex);
        //    }
        //    else
        //    {
        //        onError(ex, ob);
        //    }
        //    throw ex;
        //}

        //public void onError(Exception ex, object ob, string smenu, string trx)
        //{
        //    errorLog(LoginName, ob.GetType().ToString(), smenu, trx, ex);
        //    throw ex;
        //}
        //private void errorLog(string suid, string sclass, string smenu, string trx, Exception ex)
        //{
        //    string sql = string.Format("insert into sy_log values('{0}','{1}','{2}','{3}',now(),'{4}')",
        //                            suid, sclass, smenu, trx, ex.ToString().Replace("'", "''"));
        //    if (dbserver.openConn())
        //    {
        //        dbserver.ExecuteNonQuery(sql);
        //    }
        //}

        #endregion\

        #region CRUD Method
        public bool getTableStructure(ref DataTable dt, string tablename)
        {
            try
            {
                string s = "";
                return getTableStructure(ref dt, tablename, out s);
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        public bool getTableStructure(ref DataTable dt, string tablename, out string msg)
        {
            try
            {
                dt = new DataTable();
                msg = "";
                if (ServerProvider == "MYSQL")
                {
                    sSQL = "select * from " + tablename + " limit 0";
                }
                else
                {
                    sSQL = "select top 0 * from " + tablename ;
                }
                dt = dbserver.ExecuteQuery(sSQL, tablename);
                msg = "Success";
                return true;
            }
            catch (Exception ex)
            {

                throw ex;
            }


        }

        public DateTime getServerTime()
        {
            try
            {
                return (DateTime) dbserver.ExecuteScalar("select getdate()");
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        public virtual bool AddNewRowDB(out string msg1, int[] indexgenfieldname,
            string[] genid,bool haveHistory, params object[] fldValue)
        {
            return AddNewRowDB(out msg1, Tablename, indexgenfieldname, genid,haveHistory, fldValue);
        }

        
       
        public virtual bool AddNewRowDB(out string msg1, string tablename,
           int[] indexgenfieldname, string[] genid, bool haveHistory,params object[] fldValue)
        {
            try
            {
                msg1 = "";
                if (indexgenfieldname.Length != genid.Length)
                {
                    msg1 = "Length of Indexgenfield not equal with srting genid length";
                    return false;
                }
                if (ServerProvider.ToUpper() == "MYSQL")
                {
                    sSQL = "select * from " + tablename + " limit 0";
                }
                else
                {
                    sSQL = "select top 0 * from " + tablename ;
                }
                DataTable dt = new DataTable();
                dt = dbserver.ExecuteQuery(sSQL, tablename);

                DataRow dr = dt.NewRow();
                int i = 0;
                int j;
                foreach (int k in indexgenfieldname)
                {
                    fldValue[k] = new cDBHelper().genIDCommit(genid[k]);
                }
                //                fldValue[0] = new cDBHelper().genIDCommit("GENKDBRG");
                foreach (DataColumn dc in dt.Columns)
                {
                    if (fldValue[i].ToString().ToUpper() != "[SKIP]")
                    {
                        dr[dc.ColumnName] = fldValue[i];
                        
                       
                    }
                    i++;
                    if (i == fldValue.Count())
                        break;
                }

                if (haveHistory)
                {                    
                    dr["idcreated"] = new cApplSession().userID;
                    dr["dtcreated"] = getServerTime();
                    dr["dtupdated"] = dr["dtcreated"];
                }

                dt.Rows.Add(dr);
                if (dbserver.openConn())
                {
                    j = dbserver.SqlUpdate(dt, tablename, out msg1);

                    if (j == 1)
                    {
                        msg1 = "Sukses";
                        Msg = msg1;
                        Dbserver.CommitAndCloseConn();
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                else
                {
                    msg1 = "Database unreachable";
                    Msg = msg1;
                    return false;
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        private string genWhere(string[] keyfieldname, object[] val)
        {
            try
            {
                string sql = "";
                int i = 0;
                foreach (string s in keyfieldname)
                {
                    sql += "and " + s + "=";
                    if (val[i].GetType().ToString() == "System.String" || val[i].GetType().ToString() == "System.DateTime")
                    {
                        sql += "'" + val[i].ToString() + "' ";
                    }
                    else
                    {
                        sql += val[i].ToString() + " ";
                    }
                    i++;
                }
                return sql;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public virtual bool ExecuteUpdate(DataTable dt, out string sout)
        {
            try
            {
                if (dbserver.openConn())
                {
                    sout = "";
                    int iret = dbserver.SqlUpdate(dt, dt.TableName, out sout);
                    if (dbserver.StateConn == ConnectionState.Open)
                    {
                        dbserver.CommitAndCloseConn();
                    }
                    return iret > 0;
                }
                else
                {

                    sout = "Connection Refused";
                    return false;
                }

            }
            catch (Exception ex)
            {
                sout = "";

            }
            finally
            {
                if (dbserver.StateConn == ConnectionState.Open)
                {
                    dbserver.CommitAndCloseConn();
                }
              
            }
            return true;
        }
        public virtual bool getComboboxdata(string tablename, string stext, string sval, string where, ref DataTable dt)
        {
            try
            {
                if (where != "")
                    where = "Where " + where;
                dt = new DataTable();
                string sql = "select " + stext + "," + sval + " from " + tablename + where;
                dt = Dbserver.ExecuteQuery(sql, tablename);
                return dt.Rows.Count > 0;

            }
            catch (Exception ex)
            {

                throw ex;
            }

        }
        public virtual bool saveimage(string imgid, byte[] img)
        {
            try
            {
                DataTable dt = new DataTable();
                //img.RawFormat
                getTableStructure(ref dt, "tb_imgpool", out _msg);
                DataRow dr = dt.NewRow();
                dr["imgid"] = imgid;


                //byte[] bt = new cCommon().getBinaryImage(img);
                dr["imgdata"] = img;
                dt.Rows.Add(dr);
                dbserver.SqlUpdate(dt, "tb_imgpool", out _msg);
                return true;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        public virtual bool saveimage(byte[] img, out string sid)
        {
            try
            {
                // string sid="";
                sid = new cDBHelper().genIDCommit("GENIMGID");
                return saveimage(sid, img);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #region nanti kalo butuh
        public bool getInquirySQL(ref DataTable dt,string sql,string tbname,out string msg)
        {
            try
            {
                dt= new DataTable();
                dt = dbserver.ExecuteQuery(sql, tbname);
                msg = "";
                return dt.Rows.Count > 0;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        
        public bool getInquiryAllPartsModul(ref DataTable dt, string matname, string wrhid, string modulid)
        {
            try
            {
                string sql = "select * from vw_allparts_modul_v  where 1=1 and itnm like '%" + matname + "%' and chitno like '%" + matname + "%' and wrhid='" + wrhid + "' and paitmfbp='" + modulid + "'";
                    dt= new DataTable();
                dt=dbserver.ExecuteQuery(sql,"vw_allparts_modul_v");
                return dt.Rows.Count>0;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        public  virtual bool getInquiry(ref DataTable dt, string[] obVal, string inqTable, out string msg)
        {
            try
            {
                msg = "";
                
                

                DataTable dts = new DataTable();
                getTableStructure(ref dts, inqTable, out msg);
                sSQL = "select * from " + inqTable + " where 1=1 ";
                int i = 0;
                string stemp = "";
                foreach (object ob in obVal)
                {
                    if (ob.GetType().ToString() == "System.String")
                    {
                        stemp = ob.ToString().Trim();
                        if (stemp != string.Empty)
                        {
                            if (stemp.Substring(0, 1) == "%" && stemp.Length > 2)
                            {
                                sSQL += "and " + dts.Columns[i].ColumnName + " like '" + stemp + "'";
                            }
                            else
                            {
                                if (stemp.Substring(0, 1) != "'" && stemp.Length > 2)
                                {
                                    sSQL += "and " + dts.Columns[i].ColumnName + "='" + stemp + "'";
                                }
                                else
                                {
                                    sSQL += "and " + dts.Columns[i].ColumnName + "=" + stemp;
                                }
                            }

                        }

                    }
                    else
                    {
                        if (ob != null)
                        {
                            sSQL += "and " + dts.Columns[i].ColumnName + "=" + ob.ToString();
                        }
                    }
                    i++;
                }
                dt = Dbserver.ExecuteQuery(sSQL, inqTable);
                return dt.Rows.Count > 0;




            }
            catch (Exception ex)
            {

                throw ex;
            }
            return true;
        }

        public virtual bool getInquiry1(ref DataTable dt, string grnid, string supp, string sawal, string sakhir, string inqTable, out string msg)
        {
            try
            {
                msg = "";



                DataTable dts = new DataTable();
                sSQL = "exec " + inqTable + " '" + grnid + "', '" + supp + "', '" + sawal + "', '" + sakhir + "' ";
                dt = Dbserver.ExecuteQuery(sSQL, inqTable);
                return dt.Rows.Count > 0;
            }
            catch (Exception ex)
            {

                throw ex;
            }
            return true;
        }


        public virtual bool getInquiry2(ref DataTable dt, string mofno, string sawal, string sakhir, string inqTable, out string msg)
        {
            try
            {
                msg = "";



                DataTable dts = new DataTable();
                getTableStructure(ref dts, inqTable, out msg);
                sSQL = "select * from " + inqTable + " where 1=1 and dtcreated between '" + sawal + "' and '" + sakhir + "' ";
                if (mofno != "")
                {
                    sSQL += "and mofno ='" + mofno + "'";
                }

                dt = Dbserver.ExecuteQuery(sSQL, inqTable);
                return dt.Rows.Count > 0;
            }
            catch (Exception ex)
            {

                throw ex;
            }
            return true;
        }
        public virtual bool getInquiry3(ref DataTable dt, string wh, int bulan, int tahun, string inqTable, out string msg)
        {
            try
            {
                msg = "";

                DataTable dts = new DataTable();
                sSQL = "exec " + inqTable + " " + bulan + ", " + tahun + ", '" + wh + "' ";
                dt = Dbserver.ExecuteQuery(sSQL, inqTable);
                return dt.Rows.Count > 0;
            }
            catch (Exception ex)
            {

                throw ex;
            }
            return true;
        }
        public virtual bool getInquiry4(ref DataTable dt, string mat, string wr, int bulan, int tahun, string inqTable, out string msg)
        {
            try
            {
                msg = "";

                DataTable dts = new DataTable();
                sSQL = "exec " + inqTable + " '" + mat + "','" + wr + "'," + bulan + ", " + tahun;
                dt = Dbserver.ExecuteQuery(sSQL, inqTable);
                return dt.Rows.Count > 0;
            }
            catch (Exception ex)
            {

                throw ex;
            }
            return true;
        }
        public virtual bool getInquiry5(ref DataTable dt, string grid, string inqTable, out string msg)
        {
            try
            {
                msg = "";

                DataTable dts = new DataTable();
                sSQL = "exec " + inqTable + " '" + grid + "'";
                dt = Dbserver.ExecuteQuery(sSQL, inqTable);
                return dt.Rows.Count > 0;
            }
            catch (Exception ex)
            {

                throw ex;
            }
            return true;
        }


        
        #endregion

        public virtual bool isRecordExist( string tablename,string[] keyfieldname, object[] keyvalue)
        {
            try
            {
                
                DataTable dt = new DataTable();
                
                sSQL = "select * from " + tablename + " where 1=1  " + genWhere(keyfieldname, keyvalue);
                dt = Dbserver.ExecuteQuery(sSQL, tablename);
                return (dt.Rows.Count > 0);
                
            }
            catch (Exception ex)
            {

                throw;
            }
        }

        public virtual bool EditRowDtDB(out string msg1, string[] keyfieldname, object[] keyvalue, bool haveHistory,params object[] fldValue)
        {
            return EditRowDtDB(out msg1, Tablename, keyfieldname, keyvalue, haveHistory, fldValue);

        }
        public virtual bool EditRowDtDB(out string msg1, string tablename, string[] keyfieldname, object[] keyvalue,bool haveHistory ,params object[] fldValue)
        {
            try
            {
                msg1 = "";
                if (keyfieldname.Length != keyvalue.Length)
                {
                    msg1 = "Field Definition length not equal with value member length";
                    return false;
                }

                DataTable dt = new DataTable();
                // inqTableName = "tb_transstudio";
                sSQL = "select * from " + tablename + " where 1=1  " + genWhere(keyfieldname, keyvalue);
                dt = Dbserver.ExecuteQuery(sSQL, tablename);
                if (dt.Rows.Count > 0)
                {
                    //  inqTableName = "vw_booking";
                    DataRow dr = dt.Rows[0];
                    int i = 0;
                    int j = 0;
                    foreach (DataColumn dc in dt.Columns)
                    {
                        if (fldValue[i].ToString().ToUpper() != "[SKIP]")
                        {
                            dr[dc.ColumnName] = fldValue[i];
                        }
                        i++;
                        if (i == fldValue.Count())
                            break;
                    }
                    if (haveHistory)
                    {
                        dr["idupdated"] = new cApplSession().userID;
                        dr["dtupdated"] = getServerTime();
                    }
                    if (Dbserver.openConn())
                    {

                        j = Dbserver.SqlUpdate(dt, tablename, out msg1);
                        if (j == 1)
                        {
                            msg1 = "Records Update Success. ";
                            Dbserver.CommitAndCloseConn();
                            return true;
                        }
                        else
                        {
                            return false;
                        }
                    }
                    msg1 = "Update Success";
                    return true;
                }
                else
                {
                    //    inqTableName = "vw_booking";
                    msg1 = "Records not Found";
                    return false;
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public virtual bool deleteRowDB(string[] keyfieldname, object[] keyvalue, out string msg1)
        {
            return deleteRowDB(keyfieldname, keyvalue, Tablename, out msg1);
        }
        public virtual bool deleteRowDBSQL(string[] keyfieldname, object[] keyvalue, string tablename, out string msg1)
        {
            try
            {
                int nret = 0;
                msg1 = "";
                if (keyfieldname.Length != keyvalue.Length)
                {
                    msg1 = "Field Definition length not equal with value member length";
                    return false;
                }
                DataTable dt = new DataTable();
                // inqTableName = "tb_transstudio";
                sSQL = "select * from " + tablename + " where 1=1 " + genWhere(keyfieldname, keyvalue);
                dt = Dbserver.ExecuteQuery(sSQL, tablename);
                if (dt.Rows.Count > 0)
                {
                    //  inqTableName = "vw_booking";
                    if (Dbserver.openConn())
                    {
                        sSQL = "delete " + tablename + " where 1=1 " + genWhere(keyfieldname, keyvalue);
                        
                        //nret = Dbserver.SqlUpdate(dt, tablename, out msg1);
                        dbserver.ExecuteNonQuery(sSQL);

                        //nret=1=success else error
                        //dt.AcceptChanges();
                        nret = 1;
                        Dbserver.CommitAndCloseConn();
                    }
                    msg1 = "Delete Success";
                    return true;
                }
                else
                {
                    //    inqTableName = "vw_booking";
                    msg1 = "Records not Found";
                    return false;
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        } 

        public virtual bool deleteRowDB(string[] keyfieldname, object[] keyvalue, string tablename, out string msg1)
        {
            try
            {
                int nret = 0;
                msg1 = "";
                if (keyfieldname.Length != keyvalue.Length)
                {
                    msg1 = "Field Definition length not equal with value member length";
                    return false;
                }
                DataTable dt = new DataTable();
                // inqTableName = "tb_transstudio";
                sSQL = "select * from " + tablename + " where 1=1 " + genWhere(keyfieldname, keyvalue);
                dt = Dbserver.ExecuteQuery(sSQL, tablename);
                if (dt.Rows.Count > 0)
                {
                    //  inqTableName = "vw_booking";
                    if (Dbserver.openConn())
                    {
                        dt.Rows[0].Delete();
                        nret= Dbserver.SqlUpdate(dt, tablename, out msg1);
                        //nret=1=success else error
                        //dt.AcceptChanges();
                        Dbserver.CommitAndCloseConn();
                    }
                    msg1 = "Delete Success";
                    return true;
                }
                else
                {
                    //    inqTableName = "vw_booking";
                    msg1 = "Records not Found";
                    return false;
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        } 

        #endregion

        public virtual bool getComboboxdata(string tablename, string stext, string sval, ref DataTable dt)
        {
            try
            {
                dt= new DataTable();
                string sql = "select "+stext+","+sval+" from " + tablename;
                dt=Dbserver.ExecuteQuery(sql,tablename);
                return dt.Rows.Count>0;
                
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }
    }
}
